package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;


import util.Constants;

import db.DbController;

public class DbApproveds {
    public static Logger LOG = Logger.getLogger("db.DbApproveds");

    public static boolean addApproved(int dictID, int wordID, String word, String meaning) {
        boolean bOK = false;
        int indict = 0;
        Connection cnn = null;
        CallableStatement cstat = null;
        PreparedStatement pstatUpdate = null;
        ResultSet rs = null;

        try {
            cnn = DbController.getConnection();
            pstatUpdate = cnn.prepareStatement("update m_approveds set m_meaning=N'" + meaning + "' where m_word=N'"
                    + word + "' and m_dictID=" + dictID);
            ///////////////
            cstat = cnn.prepareCall("{call m_approveds_insert(?,?,?)}");
            cstat.setString(1, word);
            cstat.setString(2, "N'" + meaning + "'");
            cstat.setInt(3, dictID);
            
            if (pstatUpdate.executeUpdate() == 0) {
                cstat.executeUpdate();            
            }
            
            rs = cstat.executeQuery("select m_indict from m_words where m_id=" + wordID);
            if (rs.next()) {
                indict = rs.getInt("m_indict");
            }
            DbWords.deleteWord(wordID);
            bOK = true;
        } catch (Exception ex) {
            LOG.error("addApproveds", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatUpdate != null) {
                    pstatUpdate.close();
                }
                if (cstat != null) {
                    cstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        if (bOK) {
            core.Dictionary dict = DbDictionaries.getDictionary(dictID);
            if (dict != null) {
                if (indict == 1) {
                    dict.remove1Word(word);
                }
                dict.add1Word(word, meaning);
            }
        }
        return bOK;
    }

    public static boolean addApproved(int dictID, int wordID, String meaning) {
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        String strWord = Constants.BLANK_STRING;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_word from m_words where m_id=" + wordID);
            rs = pstat.executeQuery();
            if (rs.next()) {
                strWord = rs.getString("m_word");
            }
        } catch (Exception ex) {
            LOG.error("addApproved(int,int,String)", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {

            }
        }
        if (strWord.equals(Constants.BLANK_STRING)) {
            return false;
        } else {
            return addApproved(dictID, wordID, strWord, meaning);
        }
    }
}

 /*   public static boolean addApproved(int dictID, int wordID, String word, String meaning, String sessionID) {
        boolean bOK = false;
        int indict = 0;
        Connection cnn = null;
        PreparedStatement pstat = null;
        PreparedStatement pstatUpdate = null;
        ResultSet rs = null;

        try {
            cnn = DbController.getConnection();
            pstatUpdate = cnn.prepareStatement("update m_approveds set m_meaning=N'" + meaning + "', m_sessionID = '"
                    + sessionID + "' where m_word=N'" + word + "' and m_dictID=" + dictID);
            pstat = cnn.prepareStatement("insert into m_approveds(m_word,m_meaning,m_dictID,m_sessionID) values(N'"
                    + word + "',N'" + meaning + "'," + dictID + ",'" + sessionID + "')");
            if (pstatUpdate.executeUpdate() == 0) {
                pstat.executeUpdate();
            }
            rs = pstat.executeQuery("select m_indict from m_words where m_id=" + wordID);
            if (rs.next()) {
                indict = rs.getInt("m_indict");
            }
            DbWords.deleteWord(wordID);
            bOK = true;
        } catch (Exception ex) {
            LOG.error("addApproveds", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatUpdate != null) {
                    pstatUpdate.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        if (bOK) {
            core.Dictionary dict = DbDictionaries.getDictionary(dictID);
            if (dict != null) {
                if (indict == 1) {
                    dict.remove1Word(word);
                }
                dict.add1Word(word, meaning);
            }
        }
        return bOK;
    }

    public static boolean addApproved(int dictID, String word, String meaning, String sessionID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        PreparedStatement pstatUpdate = null;
        ResultSet rs = null;

        try {
            cnn = DbController.getConnection();
            pstatUpdate = cnn.prepareStatement("update m_approveds set m_meaning=N'" + meaning + "', m_sessionID = '"
                    + sessionID + "' where m_word=N'" + word + "' and m_dictID=" + dictID);
            pstat = cnn.prepareStatement("insert into m_approveds(m_word,m_meaning,m_dictID,m_sessionID) values(N'"
                    + word + "',N'" + meaning + "'," + dictID + ",'" + sessionID + "')");
            if (pstatUpdate.executeUpdate() == 0) {
                pstat.executeUpdate();
            }
            bOK = true;
        } catch (Exception ex) {
            LOG.error("addApproveds", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatUpdate != null) {
                    pstatUpdate.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        if (bOK) {
            core.Dictionary dict = DbDictionaries.getDictionary(dictID);
            if (dict != null) {
                if (dict.existWord(word)) {
                    dict.remove1Word(word);
                }
                dict.add1Word(word, meaning);
            }
        }
        return bOK;
    }
}
*/